package org.xhnews.database;

import java.util.Enumeration;

import javax.microedition.io.file.FileSystemRegistry;

import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseException;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.DatabaseIOException;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;
import net.rim.device.api.ui.UiApplication;
import net.rim.device.api.ui.component.Dialog;

import org.xhnews.constant.Constant;
import org.xhnews.log.RaspberryLogger;
import org.xhnews.to.RssChannel;
import org.xhnews.utils.StringUtil;

public class DataBase {
	private Database d;
	private static final String DATABASE_NAME = "xhnews.db";
	private static final String DB_TABLE_CHANNELS = "T_NewsChannels";
	private static final String DB_TABLE_ITEMS = "T_NewsItems";
	private static final String DB_TABLE_WEATHERS = "T_Weathers";
	private static final String DB_TABLE_PROVINCES = "T_Provinces";
	private static final String DB_TABLE_CITIES = "T_Cities";
	private static final String DB_TABLE_CONTACTS = "T_Contact";
	private static final String DB_TABLE_MESSAGES = "T_Messages";
	private static final String DB_TABLE_SENDLOG = "T_SendLog";
	private static final String DB_TABLE_RSSITEMS = "T_RssItems";
	private static final String DB_TABLE_RSSCHANNELS = "T_RssChannel";

	private static final String DB_RSSCHANNELS_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_RSSCHANNELS
			+ " (_id integer primary key autoincrement,link varchar(100) ,name varchar(50) ,keycontain varchar(50),keynotcontain varchar(50),searchregion integer default 0"
			+ ");";
	private static final String DB_RSSITEMS_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_RSSITEMS
			+ " (_id integer primary key autoincrement,title text not null,"
			+ " link varchar(50) ,"
			+ " description varchar(50)  default \"none\","
			+ " pubDate varchar(25) ,source varchar(50) ,"
			+ " author varchar(50) , isRead integer  default 0,channelId integer );";
	private static final String DB_CHANNELS_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_CHANNELS
			+ " (link varchar(50) not null primary key, "
			+ "name text not null, enable integer  default 1,isDel integer  default 0,max_news_id varchar(50) default '0');";
	private static final String DB_ITEMS_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_ITEMS
			+ " (_id varchar(20) not null primary key, link varchar(50) ,"
			+ " title text , isRead integer  default 0,isFavor integer default 0,"
			+ " attachment text ,"
			+ " pubDate varchar(25),channelLink varchar(50),"
			+ " content text);";
	private static final String DB_WEATHERS_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_WEATHERS
			+ " (_id INTEGER PRIMARY KEY autoincrement, date varchar(50), temperature varchar(50) ,"
			+ " wind varchar(50) , description varchar(50) ,"
			+ " weatherPic1 varchar(50) ,weatherPic2 varchar(50) , cityCode INTEGER );";
	private static final String DB_CONTACTS_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_CONTACTS
			+ "(_id varchar(10) not null PRIMARY KEY,name varchar(50) , groupid varchar(10)"
			+ ", type varchar(2) );";
	private static final String DB_PROVINCE_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_PROVINCES
			+ "(_id integer PRIMARY KEY, name varchar(50) );";
	private static final String DB_CITIES_CREATE = "create table IF NOT EXISTS  "
			+ DB_TABLE_CITIES
			+ "(_id integer PRIMARY KEY,name varchar(50) ,code integer ,provinceID integer );";
	private static final String DB_MESSAGES_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_MESSAGES
			+ "(_id varchar(20) not null PRIMARY KEY, title text , date varchar(25) ," 
			+" sendername varchar(50) , isRead integer not null default 0, content text , attachment text );"; 
	private static final String DB_SENDLOG_CREATE = "create table IF NOT EXISTS "
			+ DB_TABLE_SENDLOG
			+ "(_id varchar(10) not null PRIMARY KEY,lastTime varchar(25));";

	
	public void init() {
		create();
		try {
			Statement st;
			st = d.createStatement(DB_RSSCHANNELS_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_RSSITEMS_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_CHANNELS_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_ITEMS_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_WEATHERS_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_CONTACTS_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_PROVINCE_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_CITIES_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_MESSAGES_CREATE);
			st.prepare();
			st.execute();
			
			st = d.createStatement(DB_SENDLOG_CREATE);
			st.prepare();
			st.execute();
		} catch (DatabaseException e) {
			e.printStackTrace();
			RaspberryLogger.getInstance().info(e.getMessage());
		}
		close();
	}
    public boolean create(){
		if (d!=null)return true; 
		try {
			// Determine if an SDCard is present 
	        boolean sdCardPresent = false;
	        String root = null;
	        Enumeration e = FileSystemRegistry.listRoots();
	        while (e.hasMoreElements())
	        {
	            root = (String)e.nextElement();
	            if(root.equalsIgnoreCase("SDCard/"))
	            {
	                sdCardPresent = true;
	            }     
	        }            
	        if(!sdCardPresent)
	        {
	            UiApplication.getUiApplication().invokeLater(new Runnable()
	            {
	                public void run()
	                {
	                    Dialog.alert("This application requires an SD card to be present. Exiting application...");
	                    System.exit(0);            
	                } 
	            });        
	        }else{
	        	URI dbURI = URI.create("/SDCard/"+Constant.PATH_DB + DATABASE_NAME);
	        	RaspberryLogger.getInstance().info(dbURI.toString());
				d = DatabaseFactory.openOrCreate(dbURI);
				if(d!=null)	return true;
	        }
			
		} catch (Exception e) {
			e.printStackTrace();
			RaspberryLogger.getInstance().info("db open "+e.getMessage());
		}
		return false;
    }

	public boolean open() {
		try {
			URI dbURI = URI.create("/SDCard/" + Constant.PATH_DB
					+ DATABASE_NAME);
			RaspberryLogger.getInstance().info(dbURI.toString());
			d = DatabaseFactory.open(dbURI);
			if (d != null)
				return true;
		} catch (Exception e) {
			e.printStackTrace();
			RaspberryLogger.getInstance().info("db open " + e.getMessage());
		}
		return false;

	}

	public void close() {
		try {
			d.close();
			RaspberryLogger.getInstance().info("db close ");
		} catch (DatabaseIOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int getChannelCount() {
		try {
			String sql = "select count(link) from " + DB_TABLE_CHANNELS
					+ " where isDel=0";
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			c.next();
			Row r = c.getRow();
			int cnt = r.getInteger(0);
			return cnt;
		} catch (Exception e) {
			e.printStackTrace();
			RaspberryLogger.getInstance().info(e.getMessage());
			return -1;
		}
	}

	public boolean setChannelMaxNewsID(String channellink, String maxid) {
		try {
			String update_sql = "update " + DB_TABLE_CHANNELS
					+ " set max_news_id=? where link=?";
			Statement st = d.createStatement(update_sql);
			st.prepare();
			st.bind(1, maxid);
			st.bind(2, channellink);
			st.execute();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			RaspberryLogger.getInstance().info(e.getMessage());
			return false;
		}

	}

	public void delNoChannelNews() {
		try {
			String sql = "delete from " + DB_TABLE_ITEMS
					+ " where isfavor=0 and channelLink in (select link from "
					+ DB_TABLE_CHANNELS + " where isDel=1 )";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.execute();
			return;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return;
		}
	}

	public void insertChannel(String filename,String title,String max_news_id) {
		try {
			String query_sql = "select name from " + DB_TABLE_CHANNELS
					+ " where link=?";
			Statement st = d.createStatement(query_sql);
			st.prepare();
			st.bind(1, filename);
			Cursor c = st.getCursor();
			RaspberryLogger.getInstance().info(query_sql);
			if (!c.next()) {
				String sql = "insert into " + DB_TABLE_CHANNELS
						+ " (link,name,max_news_id) values (?,?,?)";
				st = d.createStatement(sql);
				st.prepare();
				st.bind(1, filename);
				st.bind(2, title);
				st.bind(3, max_news_id);
				st.execute();
			} else {
				String update_sql = "update " + DB_TABLE_CHANNELS
						+ " set name= ?, max_news_id=? where link= ? ";
				st = d.createStatement(update_sql);
				st.prepare();
				st.bind(1, title);
				st.bind(2, max_news_id);
				st.bind(3, filename);
				st.execute();
			}
			c.close();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();		
		}
	}

	public boolean setChannelEnable(String fileName, boolean enable) {
		try {
			String sql = "update " + DB_TABLE_CHANNELS + " set enable=? where link=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, enable?"1":"0");
			st.bind(2, fileName);
			st.execute();
			return true;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	public Cursor getAllEnableChannels() {
		try {
			String sql = "select link,name,enable,max_news_id from "
					+ DB_TABLE_CHANNELS + " where enable=1 and  isDel=0";
			RaspberryLogger.getInstance().info(sql);
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			e.printStackTrace();
			RaspberryLogger.getInstance().error(e);
			return null;
		}
	}

	public Cursor getAllChannels() {
		try {
			String sql = "select link,name,enable,max_news_id from "
					+ DB_TABLE_CHANNELS + " where isDel=0";
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public Cursor getAllContacts() {
		try {
			String sql = "select T_Contact._id,name,groupid,type,lastTime from "
					+ DB_TABLE_CONTACTS + " left join " + DB_TABLE_SENDLOG
					+ " on " + DB_TABLE_CONTACTS + "._id=" + DB_TABLE_SENDLOG
					+ "._id" + " order by " + DB_TABLE_SENDLOG
					+ ".lastTime desc";

			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public void insertItem(String id, String filename, String title,
			String pubdate, String channelfilename, String attachment) {
		try {
			String query_sql = "select * from " + DB_TABLE_ITEMS
					+ " where _id=?";
			RaspberryLogger.getInstance().info(query_sql);
			Statement st = d.createStatement(query_sql);
			st.prepare();
			st.bind(1, id);
			Cursor c = st.getCursor();
			String sql;
			if (!c.next()) {
				sql = "insert into "
						+ DB_TABLE_ITEMS
						+ " (_id,link,title,pubDate,channelLink,attachment) values (?,?,?,?,?,?)";
				st = d.createStatement(sql);
				RaspberryLogger.getInstance().info(sql);
				st.prepare();
				st.bind(1, id);
				st.bind(2, filename);
				st.bind(3, title);
				st.bind(4, pubdate);
				st.bind(5, channelfilename);
				st.bind(6, attachment);
				st.execute();
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public boolean setItemContent(String itemID, String content) {
		try {
			String update_sql = "update " + DB_TABLE_ITEMS + " set content=? where  _id=?";
			Statement st = d.createStatement(update_sql);
			st.prepare();
			st.bind(1, StringUtil.replaceAll("\"", "'", content));
			st.bind(2, itemID);
			st.execute();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public String getItemContent(String itemID) {
		try {
			String query_sql = "select content from " + DB_TABLE_ITEMS
					+ " where  _id=?";
			Statement st = d.createStatement(query_sql);
			st.prepare();
			st.bind(1, itemID);
			Cursor c = st.getCursor();
			if (c.next()) {
				Row r = c.getRow();
				String content = r.getString(0);
				if (content != null) {
					return content;
				}
			}
			return "";
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return "";
		}
	}

	public boolean insertMessage(String id,String title,String sendername,String date,String attachment) {
		try {
			String query_sql = "select * from " + DB_TABLE_MESSAGES
					+ " where _id=\"" + id + "\"";
			Statement st = d.createStatement(query_sql);
			st.prepare();
			Cursor c = st.getCursor();
			String sql;
			if (!c.next()) {
				sql = "insert into " + DB_TABLE_MESSAGES
						+ " (_id,title,sendername,date,attachment) values (?,?,?,?,?)";
				st = d.createStatement(sql);
				st.prepare();
				st.bind(1, id);
				st.bind(2, title);
				st.bind(3, sendername);
				st.bind(4, date);
				st.bind(5, attachment);
				st.execute();
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	public void insertWeatherItem(String date,String temperature,String wind,String weatherDescription,String weatherpicture1,String weatherpicture2, int cityCode) {
		try {
			String query_sql = "select _id from " + DB_TABLE_WEATHERS+ " where date=? and cityCode=?";
			Statement st = d.createStatement(query_sql);
			st.prepare();
			st.bind(1, date);
			st.bind(2, String.valueOf(cityCode));
			Cursor c = st.getCursor();
			String sql;
			if (!c.next()) {
				sql = "insert into "
						+ DB_TABLE_WEATHERS
						+ " (date,temperature,wind,description,weatherPic1,weatherPic2,cityCode) values (?,?,?,?,?,?,?)";
				st = d.createStatement(sql);
				st.prepare();
				st.bind(1, date);
				st.bind(2, temperature);
				st.bind(3, wind);
				st.bind(4, weatherDescription);
				st.bind(5, weatherpicture1);
				st.bind(6, weatherpicture2);
				st.bind(7, String.valueOf(cityCode));
				st.execute();
			} else {
				sql = "update " + DB_TABLE_WEATHERS + " set temperature=?, wind=?, description=?, weatherPic1=?, weatherPic2=? where date=? and cityCode=?";
				st = d.createStatement(sql);
				st.prepare();
				st.bind(1, temperature);
				st.bind(2, wind);
				st.bind(3, weatherDescription);
				st.bind(4, weatherpicture1);
				st.bind(5, weatherpicture2);
				st.bind(6, date);
				st.bind(7, String.valueOf(cityCode));				
				st.execute();
			}

		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public Cursor getWeathers(int cityCode) {
		try {
			String sql = "select date,temperature,wind,description,weatherPic1,weatherPic2 from "
					+ DB_TABLE_WEATHERS
					+ " where cityCode=? order by cityCode desc limit 3";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, cityCode);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}


	public boolean setItemRead(String id, boolean read) {
		try {
			String sRead = "1";
			if (!read) {
				sRead = "0";
			}
			String sql = "Update " + DB_TABLE_ITEMS + " set isRead=? where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, sRead);
			st.bind(2, id);
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean setMessageItemRead(String id, boolean read) {
		try {
			String sRead = "1";
			if (!read) {
				sRead = "0";
			}
			String sql = "Update " + DB_TABLE_MESSAGES + " set isRead=? where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, sRead);
			st.bind(2, id);
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean setItemFavor(String id, boolean favor) {
		try {
			String sFavor = "1";
			if (!favor) {
				sFavor = "0";
			}
			String sql = "Update " + DB_TABLE_ITEMS + " set isFavor=? where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, sFavor);
			st.bind(2, id);
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public Cursor getNewsByChannel(String chXML) {
		try {
			String sql = "select _id," + DB_TABLE_ITEMS
					+ ".link,title,pubDate,isRead,attachment, " + DB_TABLE_CHANNELS
					+ ".link as channelFileName,isFavor from " + DB_TABLE_ITEMS
					+ " ," + DB_TABLE_CHANNELS + " where channelLink=? and " + DB_TABLE_CHANNELS + ".link=? order by pubDate desc";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, chXML);
			st.bind(2, chXML);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
	public Cursor getVideoItems(String chXML) {
		try {
			String sql = "select _id,link,title,pubDate,isRead,attachment,isFavor from " + DB_TABLE_ITEMS
					+ " where channelLink=? order by pubDate desc";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, chXML);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public Cursor getNewsByID(String newsid) {
		try {
			String sql = "select _id," + DB_TABLE_ITEMS
					+ ".link,title,pubDate,isRead,attachment, " + DB_TABLE_CHANNELS
					+ ".link as channelFileName,isFavor from " + DB_TABLE_ITEMS
					+ " ," + DB_TABLE_CHANNELS + " where _id=? and " + DB_TABLE_ITEMS + ".channelLink="
					+ DB_TABLE_CHANNELS + ".link";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, newsid);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public Cursor getMessages() {
		try {
			String sql = "select _id,title,sendername,date,isRead from "
					+ DB_TABLE_MESSAGES + " order by date desc";
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info("getMessages"+e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public Cursor getLatestNews() {
		try {
			String sql = "select _id," + DB_TABLE_ITEMS
					+ ".link,title,pubDate,isRead,attachment," + DB_TABLE_CHANNELS
					+ ".name as channelTitle," + DB_TABLE_CHANNELS
					+ ".link as channelFileName, isFavor from "
					+ DB_TABLE_ITEMS + ", " + DB_TABLE_CHANNELS + " where "
					+ DB_TABLE_ITEMS + ".channelLink= " + DB_TABLE_CHANNELS
					+ ".link " + "and isRead=0 order by pubDate desc limit ?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, Integer.toString(Constant.LATEST_NEWS_LIMIT));
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public Cursor getFavorNews() {
		try {
			String sql = "select _id," + DB_TABLE_ITEMS
					+ ".link,title,pubDate,isRead,attachment," + DB_TABLE_CHANNELS
					+ ".name as channelTitle," + DB_TABLE_CHANNELS
					+ ".link as channelFileName,isFavor from " + DB_TABLE_ITEMS
					+ ", " + DB_TABLE_CHANNELS + " where " + DB_TABLE_ITEMS
					+ ".channelLink= " + DB_TABLE_CHANNELS + ".link "
					+ " and isFavor=1 order by pubDate";
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public boolean insertProvince(int id,String name) {
		try {
			String sql = "select name from " + DB_TABLE_PROVINCES
					+ " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, String.valueOf(id));
			Cursor c = st.getCursor();
			if (c.next())
				return true;
			String insert_sql = "insert into " + DB_TABLE_PROVINCES
					+ " (_id , name) values (?,?)";
			st = d.createStatement(insert_sql);
			st.prepare();
			st.bind(1, String.valueOf(id));
			st.bind(2, name);
			st.execute();
			return true;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return false;
		}
	}

	public Cursor getCities(int provinceID) {
		try {
			String sql = "select _id , name,code,provinceID from "
					+ DB_TABLE_CITIES + " where provinceID=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, String.valueOf(provinceID));
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public Cursor getProvinces() {
		try {
			String sql = "select _id , name from " + DB_TABLE_PROVINCES;
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public  boolean insertCity(int id,String name,int code,int provinceid) {
		try {
			String sql = "select name from " + DB_TABLE_CITIES + " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, String.valueOf(id));
			Cursor c = st.getCursor();
			if (c.next())
				return true;
			String insert_sql = "insert into " + DB_TABLE_CITIES
					+ " (_id,name,code,provinceID) values (?,?,?,?)";
			st = d.createStatement(insert_sql);
			st.prepare();
			st.bind(1, String.valueOf(id));
			st.bind(2, name);
			st.bind(3, String.valueOf(code));
			st.bind(4, String.valueOf(provinceid));
			st.execute();
			return true;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return false;
		}
	}

	public boolean insertContact(String id,String name,String type,String groupid) {
		try {
			String sql = "select name from " + DB_TABLE_CONTACTS
					+ " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, id);
			Cursor c = st.getCursor();
			if (c.next()) {
				String update_sql = "Update " + DB_TABLE_CONTACTS
						+ " set name=?, type=?, groupid=? where _id=?";
				st = d.createStatement(update_sql);
				st.prepare();
				st.bind(1, name);
				st.bind(2, type);
				st.bind(3, groupid);
				st.bind(4, id);
				st.execute();
			} else {
				String insert_sql = "insert into " + DB_TABLE_CONTACTS
						+ " (_id,name,type,groupid) values (?,?,?,?)";
				st = d.createStatement(insert_sql);
				st.prepare();
				st.bind(1, id);
				st.bind(2, name);
				st.bind(3, type);
				st.bind(4, groupid);				
				st.execute();
			}
			return true;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return false;
		}
	}

	public Cursor getCities(String provinceID) {
		try {
			String sql = "select _id,name,code,provinceID from "
					+ DB_TABLE_CITIES + " where provinceID=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, provinceID);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public Cursor getCityByName(String cityName) {
		try {
			String sql = "select _id,name,code,provinceID from "
					+ DB_TABLE_CITIES + " where name=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, cityName);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public void updateContactLastTime(String id, String time) {
		try {
			String query_sql = "select * from " + DB_TABLE_SENDLOG
					+ " where contact_id=?";
			Statement st = d.createStatement(query_sql);
			st.prepare();
			st.bind(1, id);
			Cursor c = st.getCursor();
			if (!c.next()) {
				String sql = "insert into " + DB_TABLE_SENDLOG
						+ " (_id,lastTime) values (?,?)";
				st = d.createStatement(sql);
				st.prepare();
				st.bind(1, id);
				st.bind(2, time);
				st.execute();
			} else {
				String update_sql = "update " + DB_TABLE_SENDLOG
						+ " set lastTime=? where _id=?";
				st = d.createStatement(update_sql);
				st.prepare();
				st.bind(1, time);
				st.bind(2, id);
				st.execute();
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public int getChannelItemCount(String channelLink) {
		try {
			String sql = "select count(_id) from " + DB_TABLE_ITEMS
					+ " where channelLink=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, channelLink);
			Cursor c = st.getCursor();
			if (c.next()) {
				Row r = c.getRow();
				int res = r.getInteger(0);
				return res;
			} else {
				return 0;
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return 0;
		}
	}

	public Cursor getToDeleteNewsFileNames(String channelLink, int n) {
		try {
			String sql = "select _id,link,attachment from " + DB_TABLE_ITEMS
					+ " where channelLink=? and isFavor=0 order by _id asc limit ?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, channelLink);
			st.bind(2, n);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public Cursor getToDeleteRssItems(String channelID, int n) {
		try {
			String sql = "select _id,title,link,description,pubDate,source,author,isRead,channelId from "
					+ DB_TABLE_RSSITEMS
					+ " where channelId=? order by pubDate asc limit ?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, channelID);
			st.bind(2, n);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public void delNewsByID(String id) {
		try {
			String sql = "delete from " + DB_TABLE_ITEMS + " where _id=? and isFavor=0";
			Statement st = d.createStatement(sql);			
			st.prepare();
			st.bind(1, id);
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public boolean delChannel(String channelFileName) {
		try {
			String sql = "update " + DB_TABLE_CHANNELS + " set isDel=1 "
					+ " where link=?";
			Statement st = d.createStatement(sql);			
			st.prepare();
			st.bind(1, channelFileName);
			st.execute();
			return true;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	public void delMessage(String id) {
		try {
			String sql = "delete from " + DB_TABLE_MESSAGES + " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, id);
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public void delAllReadMessage() {
		try {
			String sql = "delete from " + DB_TABLE_MESSAGES + " where isRead=1";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public void delAllMessage() {
		try {
			String sql = "delete from " + DB_TABLE_MESSAGES;
			Statement st = d.createStatement(sql);
			st.prepare();
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public String getMessageContent(String id) {
		try {
			String sql = "select content from " + DB_TABLE_MESSAGES
					+ " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, id);
			Cursor c = st.getCursor();
			if (c.next()) {
				Row r = c.getRow();
				String content = r.getString(0);
				if (content != null) {
					return StringUtil.replaceAll("'", "\"", content);
				} else {
					return "";
				}
			} else {
				return "";
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return "";
		}
	}

	public boolean setMessageContent(String id, String content) {
		try {
			String sql = "Update " + DB_TABLE_MESSAGES + " set content=? where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, StringUtil.replaceAll("\"", "'", content));
			st.bind(2, id);
			st.execute();
			return true;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	public void delAllContact() {
		// TODO Auto-generated method stub
		try {
			String sql = "delete from " + DB_TABLE_CONTACTS;
			Statement st = d.createStatement(sql);
			st.prepare();
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public Cursor getNewsItemMatches(String keyword) {
		try {
			String sql = "select _id as id,title,content from "
					+ DB_TABLE_ITEMS;
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public Cursor getNewsMatches(String keyword) {
		try {
			String sql = "select distinct _id," + DB_TABLE_ITEMS
					+ ".link,title,pubDate,isRead,attachment," + DB_TABLE_CHANNELS
					+ ".name as channelTitle," + DB_TABLE_CHANNELS
					+ ".link as channelFileName, isFavor from "
					+ DB_TABLE_ITEMS + ", " + DB_TABLE_CHANNELS + " where "
					+ DB_TABLE_ITEMS + ".channelLink= " + DB_TABLE_CHANNELS
					+ ".link " + "and (title like \"%?%\" or content like \"%?%\")";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, keyword);
			st.bind(2, keyword);
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			return null;
		}
	}

	public Cursor getAllRssChannels() {
		try {
			String sql = "select _id,link,name,keycontain,keynotcontain,searchregion from "
					+ DB_TABLE_RSSCHANNELS;
			Statement st = d.createStatement(sql);
			st.prepare();
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public String insertRssChannel(RssChannel ch) {
		try {
			String sql = "insert into "
					+ DB_TABLE_RSSCHANNELS
					+ " (link,name,keycontain,keynotcontain,searchregion) values (?,?,?,?,?)";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, ch.getLink());
			st.bind(2, ch.getName());
			st.bind(3, ch.getKeycontain());
			st.bind(4, ch.getKeynotcontain());
			st.bind(5, ch.getSearchregion());
			st.execute();
			sql = "select _id from " + DB_TABLE_RSSCHANNELS + " where link=?";
			st = d.createStatement(sql);
			st.prepare();
			st.bind(1, ch.getLink());
			Cursor c = st.getCursor();
			if (c.next()) {
				Row r = c.getRow();
				return r.getString(0);
			} else {
				return null;
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public int getChannelRssItemCount(String channelId) {
		try {
			String sql = "select count(_id) from " + DB_TABLE_RSSITEMS
					+ " where channelId=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, channelId);
			Cursor c = st.getCursor();
			if (c.next()) {
				Row r = c.getRow();
				int res = r.getInteger(0);
				return res;
			} else {
				return 0;
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return 0;
		}
	}

	public void updateRssChannel(int id,String name,String keycontain,String keynocontain,int searchregion,String link) {
		try {
			String update_sql = "update " + DB_TABLE_RSSCHANNELS
					+ " set name=? , keycontain=? , keynotcontain=?, searchregion=?, link=? where _id=?";
			Statement st = d.createStatement(update_sql);
			st.prepare();
			st.bind(1, name);
			st.bind(2, keycontain);
			st.bind(3, keynocontain);
			st.bind(4, searchregion);
			st.bind(5, link);
			st.bind(6, id);
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info("updateRssChannel"+e.getMessage());
			e.printStackTrace();
		}
	}

	public void insertRssItem(String title,String link,String description,String pubdate,String source,String author,String channelid) {
		try {
			String query_sql = "select * from " + DB_TABLE_RSSITEMS
					+ " where link=?";
			Statement st = d.createStatement(query_sql);
			st.prepare();
			st.bind(1, link);
			Cursor c = st.getCursor();
			if (!c.next()) {
				String sql = "insert into "
						+ DB_TABLE_RSSITEMS
						+ " (title,link,description,pubDate,source,author,isRead,channelId) values (?,?,?,?,?,?,0,?)";
				st = d.createStatement(sql);
				st.prepare();
				st.bind(1, title);
				st.bind(2, link);
				st.bind(3, description);
				st.bind(4, pubdate);
				st.bind(5, source);
				st.bind(6, author);
				st.bind(7, Integer.parseInt(channelid));
				st.execute();
			}
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public void delRssChannelById(String id) {
		try {
			String sql = "delete from " + DB_TABLE_RSSCHANNELS + " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, Integer.parseInt(id));
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public void delRssItemsByChannelID(String id) {
		try {
			String sql = "delete from " + DB_TABLE_RSSITEMS
					+ " where channelId=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, Integer.parseInt(id));
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public void delRssItemsByItemID(String id) {
		try {
			String sql = "delete from " + DB_TABLE_RSSITEMS + " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, Integer.parseInt(id));
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public void setRssItemRead(String id, boolean read) {
		try {
			String sRead = "1";
			if (!read) {
				sRead = "0";
			}
			String sql = "Update " + DB_TABLE_RSSITEMS + " set isRead=? where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, sRead);
			st.bind(2, Integer.parseInt(id));
			st.execute();
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
		}
	}

	public Cursor getRssItemById(String id) {
		try {
			String sql = "select title,link,isRead,description,pubDate,source,author,channelId"
					+ " from " + DB_TABLE_RSSITEMS + " where _id=?";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, Integer.parseInt(id));
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	public Cursor getRssByChannel(String channelId) {
		try {
			String sql = "select _id,title,link,isRead,description,pubDate,source,author,channelId"
					+ " from "
					+ DB_TABLE_RSSITEMS
					+ " where channelId=? order by pubDate desc";
			Statement st = d.createStatement(sql);
			st.prepare();
			st.bind(1, Integer.parseInt(channelId));
			Cursor c = st.getCursor();
			return c;
		} catch (Exception e) {
			RaspberryLogger.getInstance().info(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
}
